unit clsTributacoes;

interface

uses uDMCI, DbTables, ubiblioCI, sysutils;

type
  TCLtributacoes = class(TObject)
  private
    ID_CODIGO: Integer;
  public
    DESCRICAO,
    MENSAGEM,
    MODBC,
    TIPOIMPOSTO : String;
    BASEDECALCULO,
    ALIQUOTA,
    SETORNAIMPRESSORA,
//    IMP_ID_IMPOSTO,
    IMP_SIT_TRIBUTARIA,
    PDIF,
    PCREDSN,
    ALIQUOTA_INTERNA : Double;
    SOMA_VLRMERCADORIA,
    SOMA_VLRSEGURO,
    SOMA_VLRFRETE,
    SOMA_VLROUTRAS,
    SOMA_VLRDESCONTO,
    SOMA_VLRIPI,
    SOMA_VLRIMPORTACAO : String;


    function Gravar(P_ID_CODIGO: Integer): Byte;
    function Excluir(P_ID_CODIGO: Integer): Byte;
    procedure Consultar(var p_q: TQuery);
    function RetornaMaiorCodigo: Integer;
    procedure Consultar2(var p_q: TQuery; P_DESCRICAO: string);
    procedure ConsultarCST(var p_q: TQuery);
    procedure Consultar3(var p_q: TTable);
    procedure ConsultarICMS(var p_q: TQuery);
    procedure ConsultarCOFINS(var p_q: TQuery);
    procedure ConsultarIPI(var p_q: TQuery);
    procedure ConsultarISSQN(var p_q: TQuery);
    procedure ConsultarPIS(var p_q: TQuery);
    procedure ConsultarCSTRel(var p_q: TQuery);
  end;

implementation

{ TCodFiscal }

procedure TCLtributacoes.Consultar(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT I.ID_CODIGO,I.DESCRICAO,NVL(I.BASEDECALCULO,0) AS BASEDECALCULO,NVL(I.ALIQUOTA,0) AS ALIQUOTA, I.MENSAGEM,I.SETORNAIMPRESSORA, '+
              '        I.IMP_SIT_TRIBUTARIA,I.TIPOIMPOSTO,NVL(I.MODBC,0) AS MODBC, NVL(I.PDIF,0) AS PDIF, NVL(I.PCREDICMS,0) AS PCREDICMS, '+
              '        I.ID_CODIGO||'+#39+' - '+#39+'||I.DESCRICAO AS Descri, NVL(I.ALIQUOTA_INTERNA,0) AS ALIQUOTA_INTERNA, I.SOMA_VLRMERCADORIA, '+
              '        I.SOMA_VLRSEGURO, I.SOMA_VLRFRETE, I.SOMA_VLROUTRAS, I.SOMA_VLRDESCONTO, I.SOMA_VLRIPI, I.SOMA_VLRIMPORTACAO, C.CST '+
              ' FROM IMPOSTOS I LEFT OUTER JOIN CODIGOST C ON (C.CODIGO=IMP_SIT_TRIBUTARIA) ');
end;

procedure TCLtributacoes.ConsultarICMS(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT I.ID_CODIGO,I.DESCRICAO,NVL(I.BASEDECALCULO,0) AS BASEDECALCULO,NVL(I.ALIQUOTA,0) AS ALIQUOTA, I.MENSAGEM,I.SETORNAIMPRESSORA, '+
              '        I.IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,NVL(I.MODBC,0) AS MODBC, NVL(I.PDIF,0) AS PDIF, NVL(I.PCREDICMS,0) AS PCREDICMS, '+
              '        I.ID_CODIGO||'+#39+' - '+#39+'||I.DESCRICAO AS Descri, NVL(I.ALIQUOTA_INTERNA,0) AS ALIQUOTA_INTERNA, I.SOMA_VLRMERCADORIA, '+
              '        I.SOMA_VLRSEGURO, I.SOMA_VLRFRETE, I.SOMA_VLROUTRAS, I.SOMA_VLRDESCONTO, I.SOMA_VLRIPI, I.SOMA_VLRIMPORTACAO, C.CST ' +
              ' FROM IMPOSTOS I LEFT OUTER JOIN CODIGOST C ON (C.CODIGO=I.IMP_SIT_TRIBUTARIA) ' +
              ' WHERE I.TIPOIMPOSTO = '+#39+'ICMS'+#39+' ORDER BY I.DESCRICAO');
end;

procedure TCLtributacoes.ConsultarCSTRel(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT CODIGO, SIMPLESNACIONAL, CST, DESCRICAO FROM CODIGOST ');
end;


procedure TCLtributacoes.ConsultarIPI(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT I.ID_CODIGO,I.DESCRICAO,NVL(I.BASEDECALCULO,0) AS BASEDECALCULO,NVL(I.ALIQUOTA,0) AS ALIQUOTA, I.MENSAGEM,I.SETORNAIMPRESSORA, '+
              '        I.IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,NVL(I.MODBC,0) AS MODBC, NVL(I.PDIF,0) AS PDIF, NVL(I.PCREDICMS,0) AS PCREDICMS, '+
              '        I.ID_CODIGO||'+#39+' - '+#39+'||I.DESCRICAO AS Descri, NVL(I.ALIQUOTA_INTERNA,0) AS ALIQUOTA_INTERNA, I.SOMA_VLRMERCADORIA, '+
              '        I.SOMA_VLRSEGURO, I.SOMA_VLRFRETE, I.SOMA_VLROUTRAS, I.SOMA_VLRDESCONTO, I.SOMA_VLRIPI, I.SOMA_VLRIMPORTACAO, C.CST ' +
              ' FROM IMPOSTOS I LEFT OUTER JOIN CODIGOST C ON (C.CODIGO=I.IMP_SIT_TRIBUTARIA) ' +
              ' WHERE TIPOIMPOSTO = '+#39+'IPI'+#39+' ORDER BY DESCRICAO');
end;

procedure TCLtributacoes.ConsultarPIS(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT I.ID_CODIGO,I.DESCRICAO,NVL(I.BASEDECALCULO,0) AS BASEDECALCULO,NVL(I.ALIQUOTA,0) AS ALIQUOTA, I.MENSAGEM,I.SETORNAIMPRESSORA, '+
              '        I.IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,NVL(I.MODBC,0) AS MODBC, NVL(I.PDIF,0) AS PDIF, NVL(I.PCREDICMS,0) AS PCREDICMS, '+
              '        I.ID_CODIGO||'+#39+' - '+#39+'||I.DESCRICAO AS Descri, NVL(I.ALIQUOTA_INTERNA,0) AS ALIQUOTA_INTERNA, I.SOMA_VLRMERCADORIA, '+
              '        I.SOMA_VLRSEGURO, I.SOMA_VLRFRETE, I.SOMA_VLROUTRAS, I.SOMA_VLRDESCONTO, I.SOMA_VLRIPI, I.SOMA_VLRIMPORTACAO, C.CST ' +
              ' FROM IMPOSTOS I LEFT OUTER JOIN CODIGOST C ON (C.CODIGO=I.IMP_SIT_TRIBUTARIA) ' +
              ' WHERE TIPOIMPOSTO = '+#39+'PIS'+#39+' ORDER BY DESCRICAO');
end;

procedure TCLtributacoes.ConsultarCOFINS(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT I.ID_CODIGO,I.DESCRICAO,NVL(I.BASEDECALCULO,0) AS BASEDECALCULO,NVL(I.ALIQUOTA,0) AS ALIQUOTA, I.MENSAGEM,I.SETORNAIMPRESSORA, '+
              '        I.IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,NVL(I.MODBC,0) AS MODBC, NVL(I.PDIF,0) AS PDIF, NVL(I.PCREDICMS,0) AS PCREDICMS, '+
              '        I.ID_CODIGO||'+#39+' - '+#39+'||I.DESCRICAO AS Descri, NVL(I.ALIQUOTA_INTERNA,0) AS ALIQUOTA_INTERNA, I.SOMA_VLRMERCADORIA, '+
              '        I.SOMA_VLRSEGURO, I.SOMA_VLRFRETE, I.SOMA_VLROUTRAS, I.SOMA_VLRDESCONTO, I.SOMA_VLRIPI, I.SOMA_VLRIMPORTACAO, C.CST ' +
              ' FROM IMPOSTOS I LEFT OUTER JOIN CODIGOST C ON (C.CODIGO=I.IMP_SIT_TRIBUTARIA) ' +
              ' WHERE TIPOIMPOSTO = '+#39+'COFINS'+#39+' ORDER BY DESCRICAO');
end;

procedure TCLtributacoes.ConsultarISSQN(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT I.ID_CODIGO,I.DESCRICAO,NVL(I.BASEDECALCULO,0) AS BASEDECALCULO,NVL(I.ALIQUOTA,0) AS ALIQUOTA, I.MENSAGEM,I.SETORNAIMPRESSORA, '+
              '        I.IMP_SIT_TRIBUTARIA,TIPOIMPOSTO,NVL(I.MODBC,0) AS MODBC, NVL(I.PDIF,0) AS PDIF, NVL(I.PCREDICMS,0) AS PCREDICMS, '+
              '        I.ID_CODIGO||'+#39+' - '+#39+'||I.DESCRICAO AS Descri, NVL(I.ALIQUOTA_INTERNA,0) AS ALIQUOTA_INTERNA, I.SOMA_VLRMERCADORIA, '+
              '        I.SOMA_VLRSEGURO, I.SOMA_VLRFRETE, I.SOMA_VLROUTRAS, I.SOMA_VLRDESCONTO, I.SOMA_VLRIPI, I.SOMA_VLRIMPORTACAO, C.CST ' +
              ' FROM IMPOSTOS I LEFT OUTER JOIN CODIGOST C ON (C.CODIGO=I.IMP_SIT_TRIBUTARIA) ' +
              ' WHERE TIPOIMPOSTO = '+#39+'ISSQN'+#39+' ORDER BY DESCRICAO');
end;

procedure TCLtributacoes.Consultar3(var p_q: TTable);
begin
  p_q.Filter := '';
  p_q.Filter := ' SELECT * FROM IMPOSTOS ORDER BY DESCRICAO ';
end;

procedure TCLtributacoes.ConsultarCST(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT * FROM CODIGOST ORDER BY CST ');
end;

procedure TCLtributacoes.Consultar2(var p_q: TQuery; P_DESCRICAO: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('SELECT * FROM IMPOSTOS WHERE DESCRICAO LIKE (:P_DESCRICAO) ORDER BY DESCRICAO');
  p_q.parambyname('P_DESCRICAO').asstring := P_DESCRICAO;
end;

function TCLtributacoes.Excluir(P_ID_CODIGO: Integer): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM IMPOSTOS WHERE ID_CODIGO= :P_ID_CODIGO');
  tq.parambyname('P_ID_CODIGO').AsInteger := P_ID_CODIGO;
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

function TCLtributacoes.Gravar(P_ID_CODIGO: Integer): Byte;
var
  tq, tq1: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;

  tq1              := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active       := False;
  tq1.sql.clear;
  tq1.params.clear;

  tq.sql.add(' SELECT * FROM IMPOSTOS WHERE ID_CODIGO= :P_ID_CODIGO ');
  tq.parambyname('P_ID_CODIGO').AsInteger := P_ID_CODIGO;
  tq.open;
  if tq.eof then begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' INSERT INTO IMPOSTOS (ID_CODIGO, '+
                '                       DESCRICAO, '+
                '                       BASEDECALCULO, '+
                '                       ALIQUOTA, '+
                '                       MENSAGEM, '+
                '                       SETORNAIMPRESSORA, '+
                '                       IMP_SIT_TRIBUTARIA, '+
                '                       TIPOIMPOSTO, '+
                '                       MODBC, '+
                '                       PCREDICMS, '+
                '                       PDIF, ' +
                '                       ALIQUOTA_INTERNA, ' +
                '                       SOMA_VLRMERCADORIA, '+
                '                       SOMA_VLRSEGURO, '+
                '                       SOMA_VLRFRETE, '+
                '                       SOMA_VLROUTRAS, '+
                '                       SOMA_VLRDESCONTO, '+
                '                       SOMA_VLRIPI, '+
                '                       SOMA_VLRIMPORTACAO ) '+
                ' VALUES(:P_ID_CODIGO, '+
                '        :P_DESCRICAO,  '+
                '        :P_BASEDECALCULO, '+
                '        :P_ALIQUOTA, '+
                '        :P_MENSAGEM, '+
                '        :P_SETORNAIMPRESSORA, '+
                '        :P_IMP_SIT_TRIBUTARIA, '+
                '        :P_TIPOIMPOSTO, '+
                '        :P_MODBC, '+
                '        :P_PCREDICMS, '+
                '        :P_PDIF, ' +
                '        :P_ALIQUOTA_INTERNA, ' +
                '        :P_SOMA_VLRMERCADORIA, '+
                '        :P_SOMA_VLRSEGURO, '+
                '        :P_SOMA_VLRFRETE, '+
                '        :P_SOMA_VLROUTRAS, '+
                '        :P_SOMA_VLRDESCONTO, '+
                '        :P_SOMA_VLRIPI, '+
                '        :P_SOMA_VLRIMPORTACAO ) ');
  end else begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' UPDATE IMPOSTOS SET DESCRICAO          = :P_DESCRICAO, '+
                '                     BASEDECALCULO      = :P_BASEDECALCULO, '+
                '                     ALIQUOTA           = :P_ALIQUOTA, '+
                '                     MENSAGEM           = :P_MENSAGEM, '+
                '                     SETORNAIMPRESSORA  = :P_SETORNAIMPRESSORA, '+
                '                     IMP_SIT_TRIBUTARIA = :P_IMP_SIT_TRIBUTARIA, '+
                '                     TIPOIMPOSTO        = :P_TIPOIMPOSTO, '+
                '                     MODBC              = :P_MODBC, '+
                '                     PCREDICMS          = :P_PCREDICMS, '+
                '                     PDIF               = :P_PDIF, '+
                '                     ALIQUOTA_INTERNA   = :P_ALIQUOTA_INTERNA, ' +
                '                     SOMA_VLRMERCADORIA = :P_SOMA_VLRMERCADORIA, '+
                '                     SOMA_VLRSEGURO     = :P_SOMA_VLRSEGURO, '+
                '                     SOMA_VLRFRETE      = :P_SOMA_VLRFRETE, '+
                '                     SOMA_VLROUTRAS     = :P_SOMA_VLROUTRAS, '+
                '                     SOMA_VLRDESCONTO   = :P_SOMA_VLRDESCONTO, '+
                '                     SOMA_VLRIPI        = :P_SOMA_VLRIPI, '+
                '                     SOMA_VLRIMPORTACAO = :P_SOMA_VLRIMPORTACAO ' +
                ' WHERE ID_CODIGO = :P_ID_CODIGO ');
  end;
  tq1.parambyname('P_ID_CODIGO').AsFloat           := P_ID_CODIGO;
  tq1.parambyname('P_DESCRICAO').AsString          := DESCRICAO;
  tq1.parambyname('P_BASEDECALCULO').AsFloat       := BASEDECALCULO;
  tq1.parambyname('P_ALIQUOTA').AsFloat            := ALIQUOTA;
  tq1.parambyname('P_MENSAGEM').AsString           := MENSAGEM;
  tq1.parambyname('P_SETORNAIMPRESSORA').AsFloat   := SETORNAIMPRESSORA;
  tq1.parambyname('P_IMP_SIT_TRIBUTARIA').AsFloat  := IMP_SIT_TRIBUTARIA;
  tq1.parambyname('P_TIPOIMPOSTO').AsString        := TIPOIMPOSTO;
  tq1.parambyname('P_MODBC').AsString              := MODBC;
  tq1.parambyname('P_PCREDICMS').AsFloat           := PCREDSN;
  tq1.parambyname('P_PDIF').AsFloat                := PDIF;
  tq1.parambyname('P_ALIQUOTA_INTERNA').AsFloat    := ALIQUOTA_INTERNA;

  tq1.parambyname('P_SOMA_VLRMERCADORIA').AsString := SOMA_VLRMERCADORIA;
  tq1.parambyname('P_SOMA_VLRSEGURO').AsString     := SOMA_VLRSEGURO;
  tq1.parambyname('P_SOMA_VLRFRETE').AsString      := SOMA_VLRFRETE;
  tq1.parambyname('P_SOMA_VLROUTRAS').AsString     := SOMA_VLROUTRAS;
  tq1.parambyname('P_SOMA_VLRDESCONTO').AsString   := SOMA_VLRDESCONTO;
  tq1.parambyname('P_SOMA_VLRIPI').AsString        := SOMA_VLRIPI;
  tq1.parambyname('P_SOMA_VLRIMPORTACAO').AsString := SOMA_VLRIMPORTACAO;

  tq1.execsql;
  tq1.close;
  tq1.Free;
  result := 1;
  tq.close;
  tq.Free;
end;

function TCLtributacoes.RetornaMaiorCodigo(): Integer;
var
  tq1: TQuery;
begin
  tq1 := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active := False;
  tq1.close;
  tq1.sql.Clear;
  tq1.params.clear;
  tq1.sql.add(' SELECT MAX(ID_CODIGO) AS ID_CODIGO FROM IMPOSTOS ');
  tq1.Open;
  if trim(tq1.FieldByName('ID_CODIGO').AsString) = '' then
    Result := 0
  else
    Result := tq1.FieldByName('ID_CODIGO').AsInteger;
  tq1.close;
  tq1.Free;
end;

end.




